﻿CREATE PROCEDURE [steve].[spSteve_GetSimilarContentAdHoc]
	@RequiredCategoryNames CategoryNameTableType readonly,
	@OptionalCategoryNames CategoryNameTableType readonly,
	@ContentTypeName varchar(50),
	@Top bigint,
	@ApprovedOnly bit,
	@ExcludeContentId uniqueidentifier
AS
	declare @totalWeight decimal(38,7)
		, @queryWeight decimal(38,7)
		, @requiredCount int

	select @totalWeight = SUM(RelevancyWeight)
	from steve.Category

	select @requiredCount = COUNT(*) 
	from @RequiredCategoryNames

	select @queryWeight = SUM(c.RelevancyWeight)
	from @OptionalCategoryNames n
		inner join Category c on c.CategoryName = n.CategoryName

	select distinct top (@Top) cdata.ContentId
		, cdata.ContentTypeName
		, cdata.CreateDate
		, cdata.CreateMembershipId
		, cdata.ExternalId
		, cdata.SiteUrlId
		, cdata.[Language]
		, cdata.LatestRevisionNumber
		, cdata.LatestApprovedRevisionNumber
		, cdata.RevisionNumber
		, cdata.[Path]
		, cdata.SystemName
		, cdata.Author
		, cdata.ApprovalDate
		, cdata.ApprovalMembershipId
		, cdata.ContentDataId
		, cdata.Height
		, cdata.MimeSubtype
		, cdata.MimeType
		, cdata.PublicationDate
		, cdata.Publisher
		, cdata.PublisherUrlId
		, cdata.RevisionComment
		, cdata.RevisionDate
		, cdata.RevisionMembershipId
		, cdata.Teaser
		, cdata.ThumbnailContentId
		, cdata.Title
		, cdata.Width
		, cdata.SiteName
		, cdata.Url
		, cdata.IsSecure
		, cdata.DefaultAuthority

		, [steve].[udfSteve_Similarity](@totalWeight, sum(commonC.RelevancyWeight), @queryWeight, rightTotal.TotalWeight) as CategorySimilarity

	from @OptionalCategoryNames names
		inner join ContentCategory commonCC on commonCC.CategoryName = names.CategoryName and commonCC.DeleteDate is null
		inner join Category commonC on commonCC.CategoryName = commonC.CategoryName
		inner join (
			select rightCC.ContentId
				, SUM(relevancyWeight) [TotalWeight]
			from steve.ContentCategory rightCC
				inner join steve.Category rightC on rightC.CategoryName = rightCC.CategoryName
			where rightCC.DeleteDate is null
			group by rightCC.ContentId) rightTotal on rightTotal.ContentId = commonCC.ContentId
		inner join (
			select req.ContentId
			from steve.ContentCategory req
				inner join @RequiredCategoryNames reqN on reqN.CategoryName = req.CategoryName
			group by req.ContentId
			having count(*) = @requiredCount) reqContent on reqContent.ContentId = commonCC.ContentId

		inner join uvSteve_ContentRevision cdata on cdata.ContentId = commonCC.ContentId
	where (@ExcludeContentId is null or @ExcludeContentId <> commonCC.ContentId)
		and ((@ApprovedOnly = 0 and cdata.RevisionNumber = cdata.LatestRevisionNumber)
			or (@ApprovedOnly = 1 and cdata.RevisionNumber = cdata.LatestApprovedRevisionNumber))
		and (@ContentTypeName is null or cdata.ContentTypeName = @ContentTypeName)
	group by cdata.ContentId
		, cdata.ContentTypeName
		, cdata.CreateDate
		, cdata.CreateMembershipId
		, cdata.ExternalId
		, cdata.SiteUrlId
		, cdata.[Language]
		, cdata.LatestRevisionNumber
		, cdata.LatestApprovedRevisionNumber
		, cdata.RevisionNumber
		, cdata.[Path]
		, cdata.SystemName
		, cdata.Author
		, cdata.ApprovalDate
		, cdata.ApprovalMembershipId
		, cdata.ContentDataId
		, cdata.Height
		, cdata.MimeSubtype
		, cdata.MimeType
		, cdata.PublicationDate
		, cdata.Publisher
		, cdata.PublisherUrlId
		, cdata.RevisionComment
		, cdata.RevisionDate
		, cdata.RevisionMembershipId
		, cdata.Teaser
		, cdata.ThumbnailContentId
		, cdata.Title
		, cdata.Width
		, cdata.SiteName
		, cdata.Url
		, cdata.IsSecure
		, cdata.DefaultAuthority
		, rightTotal.TotalWeight

	order by CategorySimilarity desc

RETURN 0